package de.smarthome.database;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import de.smarthome.rooms.RoomData;
import de.smarthome.szenario.ScenarioData;


public class DatabaseHandler extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 1;

	private static final String DATABASE_NAME = "blabla8";

	private static final String TABLE_ROOMATTRIB = "roomattrib";

	private static final String TABLE_ROOMSTATS = "roomstats";
	private static final String TABLE_SCENARIO = "scenario";

	private SQLiteDatabase db;
	private Context context;

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		// Shows the status of every item
		String createTableRoomStats = "CREATE TABLE " + TABLE_ROOMSTATS + "(" + " room " + "TEXT PRIMARY KEY, " + " light " + "INTEGER," + " color " + "INTEGER," + " curtain "
				+ "INTEGER," + " blind " + "INTEGER," + " window " + "INTEGER," + " heater " + "INTEGER" + ")";
		db.execSQL(createTableRoomStats);

		// Shows which item is in a room available
		String createTableRoomAttrib = "CREATE TABLE " + TABLE_ROOMATTRIB + "(" + " room " + "TEXT PRIMARY KEY, " + " light " + "INTEGER," + " curtain " + "INTEGER," + " blind "
				+ "INTEGER," + " window " + "INTEGER," + " heater " + "INTEGER" + ")";
		db.execSQL(createTableRoomAttrib);

		String createTableSzenario = "CREATE TABLE " + TABLE_SCENARIO + "(" + " ID " + "INTEGER PRIMARY KEY AUTOINCREMENT, " + " scenario " + "TEXT," + " room " + "TEXT, "
				+ " light " + "INTEGER," + " color " + "INTEGER," + " curtain " + "INTEGER," + " blind " + "INTEGER," + " window " + "INTEGER," + " heater " + "INTEGER" + ")";
		db.execSQL(createTableSzenario);

	}

	public boolean isDBavailable() {

		try {
			SQLiteDatabase checkDB = SQLiteDatabase.openDatabase(context.getDatabasePath(DATABASE_NAME).toString(), null, SQLiteDatabase.OPEN_READONLY);
			if (checkDB == null) {
				return false;
			} else {
				checkDB.close();
				return true;
			}
		} catch (SQLiteException e) {
			return false;
		}

	}

	public boolean isTableAvailable(String tableName) {
		String availableQuery = "SELECT * FROM sqlite_master WHERE type='table' AND name= '" + tableName + "'";
		int count = 0;
		db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(availableQuery, null);

		if (cursor != null && !cursor.isClosed()) {
			count = cursor.getCount();
			cursor.close();
		}

		if (count == 0) {
			return false;
		} else {
			return true;
		}
	}

	public boolean isTableEmpty(String tablename) {
		if (getRoomAttributesCount(tablename) == 0) {
			return true;
		} else {
			return false;
		}
	}

	public void initDBSettings() {

		if (isTableAvailable(TABLE_ROOMATTRIB) && isTableEmpty(TABLE_ROOMATTRIB)) {
			initRoomAttrib();
		}

		if (isTableAvailable(TABLE_ROOMSTATS) && isTableEmpty(TABLE_ROOMSTATS)) {
//			createSceanrioLunchTime();
//			createSceanrioDinnerTime();
//			createSceanrioBedTime();
//			createSceanrioMorning();
			initRoomStats();
		}

	}

	public void deleteTable(String tableName) {
		db.execSQL("DROP TABLE IF EXISTS " + tableName);
	}

	public String createTable(String tableName, Map<String, Integer> attrib) {


		String createTable = "CREATE TABLE " + tableName + "(";
		StringBuilder sb = new StringBuilder();
		sb.append(createTable);
		for (Map.Entry<String, Integer> elem : attrib.entrySet()) {
			sb.append(elem.getKey() + " " + elem.getValue() + ", ");
		}
		sb.deleteCharAt(sb.length() - 2);
		sb.append(");");
	
		return sb.toString();
	}

	private void initRoomAttrib() {
		// room, window, curtain, blind, light, heater
		addRoomAttrib("livingroom", 1, 1, 1, 1, 1);
		addRoomAttrib("diningroom", 1, 0, 1, 1, 1);
		addRoomAttrib("bedroom", 1, 1, 1, 1, 1);
		addRoomAttrib("floor", 0, 1, 0, 1, 0);
		addRoomAttrib("kitchen", 1, 0, 1, 1, 1);

	}

	private void initRoomStats() {
		// room light color curtain blind window heater
		
		addRoomStat("livingroom", 0, 0, 1, 1, 0, 0);
		addRoomStat("diningroom", 0, 0, 1, 1, 0, 0);
		addRoomStat("bedroom", 0, 0, 1, 1, 0, 0);
		addRoomStat("floor", 0, 0, 1, 1, 0, 0);
		addRoomStat("kitchen", 0, 0, 1, 1, 0, 0);
		
		saveStatusForScenario("standard");

	}
	
	private void createSceanrioLunchTime(){
		// room light color curtain blind window heater
		
		addRoomStat("livingroom", 1, 0, 1, 1, 0, 0);
		addRoomStat("diningroom", 1, -6670336, 1, 1, 1, 0);
		addRoomStat("bedroom", 0, 0, 1, 1, 0, 0);
		addRoomStat("floor", 0, 0, 0, 1, 0, 0);
		addRoomStat("kitchen", 0, -8782080, 1, 1, 1, 0);
		
		saveStatusForScenario("Lunchtime");
	}

	private void createSceanrioDinnerTime(){
		// room light color curtain blind window heater
		
		addRoomStat("livingroom", 1, 0, 1, 1, 1, 0);
		addRoomStat("diningroom", 1, 926286079, 1, 1, 0, 0);
		addRoomStat("bedroom", 0, 0, 0, 0, 0, 0);
		addRoomStat("floor", 0, 0, 0, 0, 0, 0);
		addRoomStat("kitchen", 0, -8782080, 0, 1, 0, 0);
		
		saveStatusForScenario("Dinnertime");
	}
	
	private void createSceanrioBedTime(){
		// room light color curtain blind window heater
		
		addRoomStat("livingroom", 0, 0, 0, 0, 0, 0);
		addRoomStat("diningroom", 0, 0, 0, 0, 0, 0);
		addRoomStat("bedroom", 0, -6119680, 0, 0, 0, 0);
		addRoomStat("floor", 0, 0, 0, 0, 0, 0);
		addRoomStat("kitchen", 0, 0, 0, 0, 0, 0);
		
		saveStatusForScenario("Bedtime");
	}
	
	private void createSceanrioMorning(){
		// room light color curtain blind window heater
		
		addRoomStat("livingroom", 0, 0, 1, 1, 1, 0);
		addRoomStat("diningroom", 0, 0, 1, 1, 1, 0);
		addRoomStat("bedroom", 1, 0, 1, 1, 1, 0);
		addRoomStat("floor", 0, 0, 1, 0, 0, 0);
		addRoomStat("kitchen", 0, 0, 1, 1, 1, 0);
		
		saveStatusForScenario("Morning");
	}
	
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ROOMATTRIB);

		onCreate(db);
	}



	public int getRoomAttributesCount(String tableName) {
		int count = 0;
		String countQuery = "SELECT  * FROM " + tableName;
		db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);

		if (cursor != null && !cursor.isClosed()) {
			count = cursor.getCount();
			cursor.close();
		}
		return count;
	}

	/********************* Scenario **************************/

	
	public void activateScenario(String scenarioName){
		
		for(ScenarioData elem: getScenarioInfo(scenarioName)){
			updateRoomStat(elem.getRoomName(), elem.getLight(), elem.getColor(), elem.getCurtain(), elem.getBlind(), elem.getWindow(), elem.getHeater());
		}
		
		
	}
	
	public List<ScenarioData> getScenarioInfo(String name) {
		String selectQuery = "SELECT * FROM " + TABLE_SCENARIO + " where scenario like " + "'" + name + "'";
		List<ScenarioData> list = new ArrayList<ScenarioData>();
		db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {
				ScenarioData scenario = new ScenarioData();
				scenario.setId(Integer.parseInt(cursor.getString(0)));
				scenario.setScenarioName(cursor.getString(1));
				scenario.setRoomName(cursor.getString(2));
				scenario.setLight(Integer.parseInt(cursor.getString(3)));
				scenario.setColor(Integer.parseInt(cursor.getString(4)));
				scenario.setCurtain(Integer.parseInt(cursor.getString(5)));
				scenario.setBlind(Integer.parseInt(cursor.getString(6)));
				scenario.setWindow(Integer.parseInt(cursor.getString(7)));
				scenario.setHeater(Integer.parseInt(cursor.getString(8)));
				list.add(scenario);
			} while (cursor.moveToNext());
		}
		return list;
	}

	public void saveStatusForScenario(String scenarioName) {
		List<RoomData> list = getAllRoomStats();
		// TABLE SCENARIO: ID scenario room light color curtain blind window
		// heater
		for (RoomData elem : list) {
			db = this.getWritableDatabase();
			ContentValues attrib = new ContentValues();
			attrib.put("scenario", scenarioName);
			attrib.put("room", elem.getRoomName());
			attrib.put("light", elem.getLight());
			attrib.put("color", elem.getColor());
			attrib.put("curtain", elem.getCurtain());
			attrib.put("blind", elem.getBlind());
			attrib.put("window", elem.getWindow());
			attrib.put("heater", elem.getHeater());
			db.insert(TABLE_SCENARIO, null, attrib);
			db.close();
		}
		

		 Log.d("SAVESCENARIO: ", "Reading all Roominformation..");
				
				 for (RoomData elem : list) {
				 String log = "ID: " + elem.getId() + ", Room: " + elem.getRoomName()
				 + ", Light: " + elem.getLight() + ", Color: " + elem.getColor() +
				 ", Curtain: "
				 + elem.getCurtain() + ", Blind: " + elem.getBlind() + ", Window: " +
				 elem.getWindow();
				
				 Log.d("Table-Room: ", log);
				
				 }

	}

	public void changeScenarioName(String newName, String oldName) {
		ContentValues values = new ContentValues();
		values.put("scenario", newName);
		String[] args = new String[] { oldName };
		db.update(TABLE_SCENARIO, values, "scenario" + " like ?", args);
	}

	public List<String> getScenarioName() {
		List<String> result = new ArrayList<String>();
		for (ScenarioData elem : getAllScenarios()) {
			if (!result.contains(elem.getScenarioName())) {
				result.add(elem.getScenarioName());
			}
		}
		return result;
	}

	public boolean deleteScenario(String scenarioName) {
		db = this.getWritableDatabase();
		return db.delete(TABLE_SCENARIO, "scenario" + " like " + "'" + scenarioName + "'", null) > 0;

	}

	public boolean deleteAllScenario() {
		return db.delete(TABLE_SCENARIO, null, null) > 0;
	}

	
	public boolean containsScenarioName(String scenarioName){
		
		String selectQuery = "SELECT  * FROM " + TABLE_SCENARIO + " WHERE scenario like " + "'" + scenarioName + "'" ;
		
		db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		return (cursor.moveToFirst());

	}
	
	public List<ScenarioData> getAllScenarios() {
		List<ScenarioData> scenarioList = new ArrayList<ScenarioData>();

		String selectQuery = "SELECT  * FROM " + TABLE_SCENARIO;

		db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// TABLE SCENARIO: ID scenario room light color curtain blind window
		// heater
		if (cursor.moveToFirst()) {
			do {
				ScenarioData scenario = new ScenarioData();
				scenario.setId(Integer.parseInt(cursor.getString(0)));
				scenario.setScenarioName(cursor.getString(1));
				scenario.setRoomName(cursor.getString(2));
				scenario.setLight(Integer.parseInt(cursor.getString(3)));
				scenario.setColor(Integer.parseInt(cursor.getString(4)));
				scenario.setCurtain(Integer.parseInt(cursor.getString(5)));
				scenario.setBlind(Integer.parseInt(cursor.getString(6)));
				scenario.setWindow(Integer.parseInt(cursor.getString(7)));
				scenario.setHeater(Integer.parseInt(cursor.getString(8)));

				scenarioList.add(scenario);
			} while (cursor.moveToNext());
		}


		return scenarioList;
	}

	/*****
	 * ****************RoomStats*********
	 * *****************/


	public List<RoomData> getAllRoomStats() {
		List<RoomData> roomList = new ArrayList<RoomData>();

		String selectQuery = "SELECT  * FROM " + TABLE_ROOMSTATS;

		db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// TABLE ROOMATTRIB: roomname light curtain blind window heater "
		if (cursor.moveToFirst()) {
			do {
				RoomData room = new RoomData();
				room.setRoomName(cursor.getString(0));
				room.setLight(Integer.parseInt(cursor.getString(1)));
				room.setColor(Integer.parseInt(cursor.getString(2)));
				room.setCurtain(Integer.parseInt(cursor.getString(3)));
				room.setBlind(Integer.parseInt(cursor.getString(4)));
				room.setWindow(Integer.parseInt(cursor.getString(5)));
				room.setHeater(Integer.parseInt(cursor.getString(6)));
				roomList.add(room);
			} while (cursor.moveToNext());
		}
		

		 Log.d("RoomSTATS: ", "Reading all Roominformation..");
				
				 for (RoomData elem : roomList) {
				 String log = "ID: " + elem.getId() + ", Room: " + elem.getRoomName()
				 + ", Light: " + elem.getLight() + ", Color: " + elem.getColor() +
				 ", Curtain: "
				 + elem.getCurtain() + ", Blind: " + elem.getBlind() + ", Window: " +
				 elem.getWindow();
				
				 Log.d("Table-Room: ", log);
				
				 }
		
		return roomList;
	}

	public int getColor(String room) {
		RoomData roomData = new RoomData();

		String selectQuery = "SELECT * FROM " + TABLE_ROOMSTATS + " WHERE room like '" + room + "'";
		db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// TABLE ROOMATTRIB: roomname light curtain blind window heater "
		if (cursor.moveToFirst()) {
			do {
				roomData.setColor(Integer.parseInt(cursor.getString(2)));
			} while (cursor.moveToNext());
		}

		return roomData.getColor();
	}

	public void updateRoomStat(String room, int light, int color, int curtain, int blind, int window, int heater) {
		db = this.getWritableDatabase();
		ContentValues roomAttrib = new ContentValues();
		roomAttrib.put("room", room);
		roomAttrib.put("light", light);
		roomAttrib.put("color", color);
		roomAttrib.put("curtain", curtain);
		roomAttrib.put("blind", blind);
		roomAttrib.put("window", window);
		roomAttrib.put("heater", heater);
		String[] args = new String[] { room };
		db.update(TABLE_ROOMSTATS, roomAttrib, "room" + " like ?", args);

		db.close();
		
	}

	
	
	public void updateRoomStats(String room, String object, int value) {
		db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(object, value);

		String[] args = new String[] { room };
		db.update(TABLE_ROOMSTATS, values, "room" + " like ?", args);

	}

	// room light color curtain blind window heater
	public void addRoomStat(String room, int light, int color, int curtain, int blind, int window, int heater) {
		db = this.getWritableDatabase();
		ContentValues roomAttrib = new ContentValues();
		roomAttrib.put("room", room);
		roomAttrib.put("light", light);
		roomAttrib.put("color", color);
		roomAttrib.put("curtain", curtain);
		roomAttrib.put("blind", blind);
		roomAttrib.put("window", window);
		roomAttrib.put("heater", heater);
		db.insert(TABLE_ROOMSTATS, null, roomAttrib);
		db.close();
	}

	/*********
	 * ************RoomAttrib********************
	 * ******/
	public List<RoomData> getAllRoomAttrib() {
		List<RoomData> roomList = new ArrayList<RoomData>();

		String selectQuery = "SELECT  * FROM " + TABLE_ROOMATTRIB;

		db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// TABLE ROOMATTRIB: roomname light curtain blind window heater "
		if (cursor.moveToFirst()) {
			do {
				RoomData room = new RoomData();
				room.setRoomName(cursor.getString(0));
				room.setLight(Integer.parseInt(cursor.getString(1)));
				room.setCurtain(Integer.parseInt(cursor.getString(2)));
				room.setBlind(Integer.parseInt(cursor.getString(3)));
				room.setWindow(Integer.parseInt(cursor.getString(4)));
				room.setHeater(Integer.parseInt(cursor.getString(5)));

				roomList.add(room);
			} while (cursor.moveToNext());
		}


		return roomList;
	}

	
	
	public void addRoomAttrib(String room, int window, int curtain, int blind, int light, int heater) {
		db = this.getWritableDatabase();
		ContentValues roomAttrib = new ContentValues();
		roomAttrib.put("room", room);
		roomAttrib.put("window", window);
		roomAttrib.put("curtain", curtain);
		roomAttrib.put("blind", blind);
		roomAttrib.put("light", light);
		roomAttrib.put("heater", heater);
		db.insert(TABLE_ROOMATTRIB, null, roomAttrib);
		db.close();
	}

}